﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;
using System.Diagnostics;

namespace University.Workshop.DataAccess
{
    public static class RateDataAccess
    {
        public const string ConnString = "Data Source=.\\SQLEXPRESS;Initial Catalog=MedicalDatabase;Integrated Security = True";

        public static Rate InsertRate(Rate rate)
        {
            if (rate != null)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [Rate](Code,Service,Price,Description,Status) VALUES(@Code,@Service,@Price,@Description,@Status)";
                    cmd.Parameters.AddWithValue("Code", rate.Code);
                    cmd.Parameters.AddWithValue("Service", rate.Service);
                    cmd.Parameters.AddWithValue("Price", rate.Price);
                    cmd.Parameters.AddWithValue("Description", rate.Description);
                    cmd.Parameters.AddWithValue("Status", 1);

                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error inserting a rate in the database");
                        }
                        return rate;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error inserting a rate in the database SQL Exception" + ex);
                    }
                }
            }
            else
                return null;
        }

        public static Rate GetRateByCode(string code)
        {
            if (!string.IsNullOrEmpty(code))
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [Rate] Where Code = @Code";
                    cmd.Parameters.AddWithValue("Code", code);
                    conn.Open();
                    try
                    {

                        SqlDataReader reader = cmd.ExecuteReader();
                        if (!reader.HasRows)
                        {
                            return null;
                        }
                        Rate rate = new Rate();
                        while (reader.Read())
                        {
                            rate.Code = reader["Code"].ToString();
                        }
                        return rate;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error obteniendo la tarifa", ex);
                    }
                }
            }
            else return null;
        }

        public static DataTable ShowRates()
        {
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                SqlCommand cmd = conn.CreateCommand();
                DataTable table = null;
                DataTable tableAux = null;
                cmd.CommandText = "SELECT Code,Service,Price,Description FROM [Rate] WHERE Status='1'";
                conn.Open();
                try
                {

                    SqlDataReader reader = cmd.ExecuteReader();
                    table = new DataTable();
                    table.Locale = CultureInfo.InvariantCulture;

                    table.Load(reader);
                    tableAux = table;

                    return tableAux;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error obteniendo la tarifa", ex);
                }

                finally
                {
                    if (table != null)
                    {
                        table.Dispose();
                    }
                }
            }
        }


        public static bool DeleteRate(string value)
        {
            if (!String.IsNullOrEmpty(value))
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE [Rate] SET [Status] = 0 WHERE [Code] = @Code";
                    cmd.Parameters.AddWithValue("Code", value);

                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error deleting a rate in the database");
                        }
                        return true;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error deleting a rate in the database SQL Exception" + ex);
                    }

                }
            }
            else
                return false;
        }

        public static bool UpdateRate(Rate rate)
        {
            if (rate != null)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE [Rate] SET [Service] = @Service,[Price] = @Price,[Description] = @Description  WHERE [Code] = @Code";

                    cmd.Parameters.AddWithValue("Code", rate.Code);
                    cmd.Parameters.AddWithValue("Service", rate.Service);
                    cmd.Parameters.AddWithValue("Price", rate.Price);
                    cmd.Parameters.AddWithValue("Description", rate.Description);

                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error updating a rate in the database");
                        }
                        return true;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error updating a rate in the database SQL Exception" + ex);
                    }

                }
            }
            else
                return false;
        }
    }
}
